﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using KPIS.DBM;
//
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_SECURITY_Users
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        //

        //public DataTable GetUserInfo(string strUserID)
        //{
        //    string strSQL;
        //    DataTable dtb = null;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "SELECT user_seq, emp_seq, user_id, password, user_name, user_status"
        //            + " FROM gerp_security.users"
        //            + " WHERE record_status not like 'D'"
        //            + (strUserID.Length > 0 ? " and user_id = " + Misc.ValueForSQL(strUserID) : "");

        //        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return dtb;
        //}

        public DataTable GetUserInfo(string strUserID, string PassWord)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Sequence", strUserID);
                dbManager.AddParameters(1, "@PassWord", PassWord);
                dbManager.AddParameters(2, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                strSQL = "SELECT u.*"
                    + ", cast(CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) as varchar) AS USER_NAME"
                    + ", p.POS_REF, p.POS_NAME, j.JOB_SITE_NAME, w.JOB_SITE_NAME AS WORKING_JOB_SITE_NAME"
                    + ", c.CLASS_NAME, e.WORKING_JOB_SITE_SEQ, w.WORK_AREA_SEQ, u.USER_PASSWORD"
                    + " FROM SYS_SC_USERS u"
                    //+ " LEFT JOIN SYS_SC_USER_ROLES ur ON u.USER_SEQ = ur.USER_SEQ"
                    //+ " LEFT JOIN SYS_SC_ROLES r ON ur.ROLE_SEQ = ur.ROLE_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e ON u.EMP_SEQ = e.EMP_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                    + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                    + " LEFT JOIN HRM_MP_JOB_SITES j ON p.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                    + " LEFT JOIN HRM_MP_JOB_SITES w ON e.WORKING_JOB_SITE_SEQ = w.JOB_SITE_SEQ"
                    + " WHERE u.RECORD_STATUS = @RecordStatus"
                    + (string.IsNullOrEmpty(strUserID) ? "" : " AND u.USER_CODE = @Sequence")
                    + (string.IsNullOrEmpty(PassWord) ? "" : " AND u.USER_PASSWORD = @PassWord");
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable GetMenuItemList(string strUserID)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@strUserID", strUserID);

                strSQL = "SELECT DISTINCT ml.MENU_ITEM_SEQ AS SEQ"
                        + ", CONCAT('<div class=font-menu>', ml.MENU_ITEM_NAME, '</div>') AS NAME"
                        + ", ml.UPPER_MENU_ITEM_SEQ  AS UpperSeq"
                        + ", ml.MENU_ITEM_PATH AS PATH"
                        + ", ml.MENU_ITEM_TARGET AS TARGET"
                        + ", ml.RECORD_STATUS"
                    + " FROM SYS_SC_USERS u"
                    + " INNER JOIN SYS_SC_USER_ROLES ur ON u.USER_SEQ = ur.USER_SEQ"
                        + " INNER JOIN SYS_SC_ROLES r ON ur.ROLE_SEQ = r.ROLE_SEQ"
                        + " INNER JOIN SYS_SC_PERMISSIONS p ON r.ROLE_SEQ = p.ROLE_SEQ"
                        + " INNER JOIN SYS_MENU_ITEMS_LIST ml ON p.MENU_ITEM_SEQ = ml.MENU_ITEM_SEQ"
                    + " WHERE u.RECORD_STATUS not like @DeleteRecord"
                        + " AND ur.RECORD_STATUS not like @DeleteRecord"
                        + " AND r.RECORD_STATUS not like @DeleteRecord"
                        + " AND p.RECORD_STATUS not like @DeleteRecord"
                        + " AND ml.RECORD_STATUS not like @DeleteRecord"
                        + (strUserID != "" ? " AND u.USER_SEQ = @strUserID" : "")
                        + " AND ml.SYSTEM_ID = 'ESS'"
                    + " ORDER BY ml.MENU_ITEM_CODE";
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }
        public bool CheckPassword(string Sequence, string OldPassword)
        {
            string strSQL;
            bool bOK = false;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@OldPassword", OldPassword);

                DataSet ds = null;
                DataRowCollection drc;

                strSQL = "SELECT *"
                    + " FROM SYS_SC_USERS"
                    + " WHERE USER_SEQ = @Sequence"
                        + " AND USER_PASSWORD = @OldPassword";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables.Count > 0)
                {
                    drc = ds.Tables[0].Rows;
                    bOK = drc.Count > 0;
                }
                return bOK;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public void ChangePassword(int Sequence, string Password, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@Password", Password);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE SYS_SC_USERS"
                    + " SET USER_PASSWORD = @Password"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE USER_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
    }
}
